from flask import Flask
import pandas as pd
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

# database configuration---------------------------------------
app.secret_key = "alskdjfwoeieiurlskdjfslkdjf"
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://root:Wyytyks_158425@localhost/ecom"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# load files===========================================================================================================
class TrendingProduct(db.Model):
    __tablename__ = 'trending_products'  # 数据库中的表名
    ID = db.Column(db.Integer, primary_key=True)
    Name = db.Column(db.String(255))
    Brand = db.Column(db.String(100))
    ImageURL = db.Column(db.Text)  # Text 类型
    ReviewCount = db.Column(db.Integer)
    Rating = db.Column(db.Numeric(3, 1))  # 修改为 Numeric(3, 1) 类型


class TrainData(db.Model):
    __tablename__ = 'clean_data'  # 数据库中的表名
    ID = db.Column(db.Integer, primary_key=True)  # int 类型
    ProdID = db.Column(db.Integer)  # int 类型
    Rating = db.Column(db.Float)  # float 类型
    ReviewCount = db.Column(db.Integer)  # int 类型
    Category = db.Column(db.Text)  # text 类型
    Brand = db.Column(db.String(255))  # varchar(255) 类型
    Name = db.Column(db.String(255))  # varchar(255) 类型
    ImageURL = db.Column(db.String(1024))  # varchar(1024) 类型
    Description = db.Column(db.Text)  # text 类型
    Tags = db.Column(db.Text)
    


class IteamData(db.Model):
    __tablename__ = 'score'  # 数据库中的表名

    username = db.Column(db.String(100), primary_key=True)  # 将 username 设置为主键
    visitorid = db.Column(db.Integer)  # 保留其他字段，但不作为主键
    itemid = db.Column(db.Integer)
    score = db.Column(db.Numeric(5, 2))




def load_and_process_data():
    with app.app_context():  # Ensure querying happens in the Flask app context
        # Load trending products
        trending_products = TrendingProduct.query.all()
        trending_products_df = pd.DataFrame([{
            'ID': p.ID,
            'Name': p.Name,
            'Brand': p.Brand,
            'ImageURL': p.ImageURL,
            'ReviewCount': p.ReviewCount,
            'Rating': p.Rating
        } for p in trending_products])

        # Load training data
        train_data = TrainData.query.all()
        train_data_df = pd.DataFrame([{
            'ID': t.ID,
            'ProdID': t.ProdID,
            'Rating': t.Rating,
            'ReviewCount': t.ReviewCount,
            'Category': t.Category,
            'Brand': t.Brand,
            'Name': t.Name,
            'ImageURL': t.ImageURL,
            'Description': t.Description,
            'Tags':t.Tags
        } for t in train_data])

        # Load item data (score)
        iteam_data = IteamData.query.all()
        iteam_data_df = pd.DataFrame([{
            'visitorid': i.visitorid,
            'itemid': i.itemid,
            'score': i.score,
            'username': i.username
        } for i in iteam_data])

        return trending_products_df, train_data_df, iteam_data_df



def save_data_to_csv(trending_products_df, train_data_df):
    trending_products_df.to_csv('trending_products2.csv', index=False)
    train_data_df.to_csv('train_data2.csv', index=False)
    
    

def main():
    trending_products, train_data, iteam_data = load_and_process_data()
    save_data_to_csv(trending_products, train_data)
    iteam_data['username'] = iteam_data['username'].str.rstrip('\r')
    iteam_data.to_csv('iteam_data_final.csv')
    

